import com.mysql.cj.jdbc.CallableStatement;
import com.mysql.cj.jdbc.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class JDBC {
    public static void main(String[] args) throws SQLException {
//        JDBC.testInsert();1

//        JDBC.testDelete();

//        JDBC.testUpdate();

        JDBC.testSelect();
    }


    public static void testSelect() throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/tmp?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8");
        ((MysqlDataSource)dataSource).setUser("zhang");
        ((MysqlDataSource)dataSource).setPassword("xijie9401");

        Scanner scanner=new Scanner(System.in);
        System.out.println("输入要查找的Id");
        int id=scanner.nextInt();

        Connection connection=dataSource.getConnection();
        String sql="select * from student where id=?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        ResultSet resultSet=statement.executeQuery();
        while (resultSet.next()){
            System.out.println(resultSet.getInt("id"));
            System.out.println();
            System.out.println(resultSet.getString("name"));
        }
        resultSet.close();
        statement.close();
        connection.close();
    }

    public static void testUpdate() throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/tmp?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8");
        ((MysqlDataSource)dataSource).setUser("zhang");
        ((MysqlDataSource)dataSource).setPassword("xijie9401");

        Scanner scanner=new Scanner(System.in);
        System.out.println("输入要修改的Id");
        int id=scanner.nextInt();
        System.out.println("输入修改后的名字");
        String name=scanner.next();

        Connection connection=dataSource.getConnection();
        String sql="update student set name=? where id=?";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setString(1,name);
        statement.setInt(2,id);
        int ret=statement.executeUpdate();

        statement.close();
        connection.close();
    }




    public static void testDelete() throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/tmp?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8");
        ((MysqlDataSource)dataSource).setUser("zhang");
        ((MysqlDataSource)dataSource).setPassword("xijie9401");

        Scanner scanner=new Scanner(System.in);
        System.out.println("输入要删除数据的Id");
        int id=scanner.nextInt();

        Connection connection=dataSource.getConnection();

        String sql="delete from student where id=?";

        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);

        int ret=statement.executeUpdate();
        statement.close();
        connection.close();
    }




    public static void testInsert() throws SQLException {
        DataSource dataSource=new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/tmp?serverTimezone=GMT&allowPublicKeyRetrieval=true&useSSL=false&characterEncoding=utf8");
        ((MysqlDataSource)dataSource).setUser("zhang");
        ((MysqlDataSource)dataSource).setPassword("xijie9401");

        Connection connection=dataSource.getConnection();

        Scanner scanner=new Scanner(System.in);
        System.out.println("输入id:");
        int id=scanner.nextInt();
        System.out.println("输入名字");
        String name=scanner.next();


        String sql="insert into student values(?,?)";
        PreparedStatement statement=connection.prepareStatement(sql);
        statement.setInt(1,id);
        statement.setString(2,name);
        int ret= statement.executeUpdate();
        if(ret!=1){
            System.out.println("插入失败!");
        }else{
            System.out.println("插入成功！");
        }
        statement.close();
        connection.close();
    }
}
